/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package AccesoADatos;

import Logica.Usuario;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

/**
 *
 * @author Fernando
 */
public class ServicioUsuario extends Servicio {
    
    private String user;
    private String pass;
    private String ip;
    private String port;
    private String db;
    
    private static String USUARIOS = "select username from all_users where username = 'ANA' ";
    private static String ROLES = "select role from dba_roles";
    private static String USERROL = "select * from DBA_ROLE_PRIVS where grantee=?";
    //private static String CREATEUSER ="CREATE USER ? IDENTIFIED BY ?"; 
    
    public ServicioUsuario(String user,String pass,String ip,String port,String db){
        this.user=user;
        this.pass=pass;
        this.ip=ip;
        this.port=port;
        this.db=db;
    }
    
    public Collection listarUsers() throws GlobalException, NoDataException{      
        try {
            conectar(user, pass,ip,port,db);       
        }
        catch(ClassNotFoundException ex)
        {
            throw new GlobalException("No se ha localizado el Driver");
        }
        
        catch (SQLException e) {
            throw new NoDataException("La base de datos no se encuentra disponible");
        }      
        
        ResultSet rs=null;
        ArrayList coleccion= new ArrayList();
        Statement stmt = null;
        try{
            stmt = conexion.createStatement();      
            rs = stmt.executeQuery(USUARIOS);
             while (rs.next()) {
                 String aux = rs.getString("username");
                 if((!(aux.equals("SYS")))&&(!(aux.equals("SYSTEM")))&&(!(aux.equals("ANONYMOUS")))){
                  coleccion.add(aux);
             }
            }
        } catch (SQLException e) {
          e.printStackTrace();
          
       throw new GlobalException("Sentencia no valida");
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
            } catch (SQLException e) {
                throw new GlobalException("Estatutos invalidos o nulos");
            }
        }
        if (coleccion == null || coleccion.size() == 0) {
            throw new NoDataException("No hay datos");
        }
        return coleccion;
    }
    
    
    public Collection listarRoles() throws GlobalException, NoDataException{      
        try {
            conectar(user, pass,ip,port,db);       
        }
        catch(ClassNotFoundException ex)
        {
            throw new GlobalException("No se ha localizado el Driver");
        }
        
        catch (SQLException e) {
            throw new NoDataException("La base de datos no se encuentra disponible");
        }      
        
        ResultSet rs=null;
        ArrayList coleccion= new ArrayList();
        Statement stmt = null;
        try{
            stmt = conexion.createStatement(); 
            rs = stmt.executeQuery(ROLES);
             while (rs.next()) {
                 String aux = rs.getString("role");
                 coleccion.add(aux);
            }
        } catch (SQLException e) {
          e.printStackTrace();
          
       throw new GlobalException("Sentencia no valida");
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
            } catch (SQLException e) {
                throw new GlobalException("Estatutos invalidos o nulos");
            }
        }
        if (coleccion == null || coleccion.size() == 0) {
            throw new NoDataException("No hay datos");
        }
        return coleccion;
    }
    
    public Collection listarRolesDeUsuario(Usuario u)throws GlobalException, NoDataException{
        try {
            conectar(user, pass,ip,port,db);       
        }
        catch(ClassNotFoundException ex)
        {
            throw new GlobalException("No se ha localizado el Driver");
        }
        
        catch (SQLException e) {
            throw new NoDataException("La base de datos no se encuentra disponible");
        }      
        
        ResultSet rs=null;
        ArrayList coleccion= new ArrayList();
        PreparedStatement stmt = null;
        try{
            stmt = conexion.prepareStatement(USERROL);
            stmt.setString(1, u.getName());
            rs = stmt.executeQuery();
             while (rs.next()) {
                 String aux = rs.getString("GRANTED_ROLE");
                 coleccion.add(aux);
            }
        } catch (SQLException e) {
          e.printStackTrace();
          
       throw new GlobalException("Sentencia no valida");
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
            } catch (SQLException e) {
                throw new GlobalException("Estatutos invalidos o nulos");
            }
        }
        if (coleccion == null || coleccion.size() == 0) {
            throw new NoDataException("No hay datos");
        }
        return coleccion;
    }
    
    public boolean newRolToUser (String n, String p) throws GlobalException, NoDataException{
        try {
            conectar(user, pass,ip,port,db);       
        }
        catch(ClassNotFoundException ex)
        {
            throw new GlobalException("No se ha localizado el Driver");
        }
        
        catch (SQLException e) {
            throw new NoDataException("La base de datos no se encuentra disponible");
        }      
        
        ResultSet rs=null;
        Statement stmt = null;
        try{
            stmt = conexion.createStatement(); 
            String ss = "GRANT " + n + " TO " + p;
            System.out.println(ss);
            stmt.executeQuery(ss);
             //JOptionPane.showMessageDialog(null, ss);
        } catch (SQLException e) {
            //return false;
          e.printStackTrace();
          
       throw new GlobalException("Sentencia no valida");
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
                return true;
            } catch (SQLException e) {
                throw new GlobalException("Estatutos invalidos o nulos");
            }
        }
    }
    public boolean newUser(String n, String p) throws GlobalException, NoDataException{      
        try {
            conectar(user, pass,ip,port,db);       
        }
        catch(ClassNotFoundException ex)
        {
            throw new GlobalException("No se ha localizado el Driver");
        }
        
        catch (SQLException e) {
            throw new NoDataException("La base de datos no se encuentra disponible");
        }      
        
        ResultSet rs=null;
        Statement stmt = null;
        try{
            stmt = conexion.createStatement(); 
            String ss = "CREATE USER " + n + " IDENTIFIED BY " + p;
            stmt.executeQuery(ss);
             //JOptionPane.showMessageDialog(null, ss);
        } catch (SQLException e) {
            //return false;
          e.printStackTrace();
          
       throw new GlobalException("Sentencia no valida");
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
                return true;
            } catch (SQLException e) {
                throw new GlobalException("Estatutos invalidos o nulos");
            }
        }
        
    }
    
    public boolean dropUser(String n) throws GlobalException, NoDataException{      
        try {
            conectar(user, pass,ip,port,db);       
        }
        catch(ClassNotFoundException ex)
        {
            throw new GlobalException("No se ha localizado el Driver");
        }
        
        catch (SQLException e) {
            throw new NoDataException("La base de datos no se encuentra disponible");
        }      
        
        ResultSet rs=null;
        Statement stmt = null;
        try{
            stmt = conexion.createStatement(); 
            String ss = "DROP USER " + n ;
            rs = stmt.executeQuery(ss);
             
        } catch (SQLException e) {
            //return false;
          e.printStackTrace();
          
       throw new GlobalException("Sentencia no valida");
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
                return true;
            } catch (SQLException e) {
                throw new GlobalException("Estatutos invalidos o nulos");
            }
        }
        
    }
    
//<<<<<<< .mine
    /////////////
    
     public boolean agregarRol(String n, String r) throws GlobalException, NoDataException{      
        try {
            conectar(user, pass,ip,port,db);       
        }
        catch(ClassNotFoundException ex)
        {
            throw new GlobalException("No se ha localizado el Driver");
        }
        
        catch (SQLException e) {
            throw new NoDataException("La base de datos no se encuentra disponible");
        }      
        
        ResultSet rs=null;
        Statement stmt = null;
        try{
            stmt = conexion.createStatement(); 
            String ss = "GRANT " + r + " TO " + n;
            rs = stmt.executeQuery(ss);
             
        } catch (SQLException e) {
            //return false;
          e.printStackTrace();
          
       throw new GlobalException("Sentencia no valida");
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
                return true;
            } catch (SQLException e) {
                throw new GlobalException("Estatutos invalidos o nulos");
            }
        }
        
    }
     
       public boolean eliminarRol(String n, String r) throws GlobalException, NoDataException{      
        try {
            conectar(user, pass,ip,port,db);       
        }
        catch(ClassNotFoundException ex)
        {
            throw new GlobalException("No se ha localizado el Driver");
        }
        
        catch (SQLException e) {
            throw new NoDataException("La base de datos no se encuentra disponible");
        }      
        
        ResultSet rs=null;
        Statement stmt = null;
        try{
            stmt = conexion.createStatement(); 
            String ss = "REVOKE " + r + " FROM " + n;
            rs = stmt.executeQuery(ss);
             
        } catch (SQLException e) {
            //return false;
          e.printStackTrace();
          
       throw new GlobalException("Sentencia no valida");
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
                return true;
            } catch (SQLException e) {
                throw new GlobalException("Estatutos invalidos o nulos");
            }
        }
        
    }
    
//=======
    public boolean removeCategoryFromUser(String cat, String us) throws GlobalException, NoDataException{      
        try {
            conectar(user, pass,ip,port,db);       
        }
        catch(ClassNotFoundException ex)
        {
            throw new GlobalException("No se ha localizado el Driver");
        }
        
        catch (SQLException e) {
            throw new NoDataException("La base de datos no se encuentra disponible");
        }      
        
        ResultSet rs=null;
        Statement stmt = null;
        try{
            stmt = conexion.createStatement(); 
            String ss = "REVOKE " + cat +" FROM "+us ;
            rs = stmt.executeQuery(ss);
             
        } catch (SQLException e) {
            //return false;
          e.printStackTrace();
          
       throw new GlobalException("Sentencia no valida");
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
                return true;
            } catch (SQLException e) {
                throw new GlobalException("Estatutos invalidos o nulos");
            }
        }
        
    }//>>>>>>> .r70
}
